/**
 * LibManDAOImpl.java
 *
 * @author:  John Thomason
 *  Course:  CS223
 *  Assign:  Final Exam
 * Seminar:  6
 *    Date:  Feb 16, 2009
 */

// Assigned Package
package libman.db;

// Package Imports

import libman.*;
import libman.Book.*;
import java.util.*;
import java.io.*;
import java.sql.*;


/**
 * LibManDAOImpl Class
 */
public class LibManDAOImpl
implements LibManDAO
{
    //Instance Variables
    private String connUrl, host, user, password,
                   databaseName, dbDriver, port;
    private static Connection dbConn;
    private File   sqlScript = new File("src/libman/db/LibraryManager.sql");


    //Constructors
    public LibManDAOImpl() throws LibManException {
        this(new File("src/libman/db/DbConfig.xml"));
    }
    public LibManDAOImpl(File dbConfig) throws LibManException {
        try {

            //Load the DB properties from a XML properties file
            Properties p = new Properties();
            p.loadFromXML(new FileInputStream(dbConfig));

            //Save a copy of the connection parameters in this object
            port         = p.getProperty("port");
            host         = p.getProperty("host");
            user         = p.getProperty("user");
            password     = p.getProperty("password");
            databaseName = p.getProperty("databaseName");
            dbDriver     = p.getProperty("dbDriver");
            
            //Register the database driver
            Class.forName(dbDriver);

            boolean notConnected = true;
            int     maxRetry     = 3;
            int     retryCnt     = 0;
            while (notConnected){
                try {
                    //Create the single instance of the database connection
                    connUrl  = createConnUrl();
                    dbConn   = DriverManager.getConnection(connUrl, p);
                    notConnected = false;
                    //Create db tables if new db is created
                    if (p.getProperty("create").equals("true")){
                        SqlTables.construct(dbConn,sqlScript);
                    }//if createDb
                }//try
                catch (SQLException se){
                    if (retryCnt >= maxRetry)
                        throw new SQLException(
                                "DB Connection Max Retry Count Reached! ", se);
                    retryCnt++;
                    p.setProperty("create", "true");
                }//catch

            }//while not connected

        }//try
        catch(Exception e){
            throw new LibManException(e.getMessage());
        }//catch

    }//LibManDAOImpl Constructors


// Implementation of required methods *****************************************

    /** -----------------------------------------------------------------------
     * Create a new book record in the database
     *
     * @param book      The book object to add to the db
     * @throws libman.LibManException
     */
    public void create(Book book) throws LibManException {
        if (book != null && !exists(Field.ID,book.getId())){
            String q = String.format("insert into app.books " +
                                     "values ('%s','%s','%s','%s')",
                                     book.getId(),
                                     book.getTitle(),
                                     book.getAuthor(),
                                     book.getSummary());
            try {
                Connection con = getConnInstance();
                Statement stmt = con.createStatement();
                System.err.printf("Rows Affected: %d\n", stmt.executeUpdate(q));
            }//try
            
            catch (Exception e){
                throw new LibManException("LibManDAOImpl.create::" +
                                           e.getMessage());
            }//catch
        }//if

        else {
            throw new LibManException("LibManDAOImpl.create::Book ID: '" +
                                      ((book != null)?book.getId():"null") +
                                      "' already exists.");
        }//else
        
    }//create()

    /** -----------------------------------------------------------------------
     * Find the needle in the haystack...
     *
     * @param needle    The string in which to search
     * @return          The book containing the needle
     * @throws libman.LibManException
     */
    public Book read(String needle) throws LibManException {
        Book book   = null;
        Field field = null;
        //Search for searchStr in each of the enumerated fields
        for (Field f: field.values()){
            if (exists(f,needle)){
                field = f;
                break;
            }//if
        }//for
        if (field != null){
            Connection con = getConnInstance();
            String q = String.format("select * from app.books where %s='%s'",
                                      getFieldName(field),
                                      needle);
            try {
                Statement stmt = con.createStatement();
                //System.err.println(q);
                ResultSet   rs = stmt.executeQuery(q);
                if (rs.next()){ //safety net
                    book = new Book(rs.getString("id"),
                                    rs.getString("title"),
                                    rs.getString("author"),
                                    rs.getString("summary"));
                }//if
            }//try
            catch (Exception e){
                //e.printStackTrace(System.err);
                throw new LibManException("LibManDAOImpl.read::" +
                                           e.getMessage());
            }//catch
        }//if book exists; return it

        else {
            throw new LibManException("WARNING: " + needle +
                                      " does NOT exist in DB...");
        }//else book doesn't exist

        return book;
        
    }//read()

    /** -----------------------------------------------------------------------
     * Return an array of all existing books in the db
     *
     * @return      A book array containing all books
     * @throws libman.LibManException
     */
    public Book[] readAll() throws LibManException {
        String count = "select count(id) from app.books";
        String ids   = "select id from app.books order by title";
        Book[] books = null;
        // try to get all records from the books table
        try { 
            Connection con = getConnInstance();
            Statement stmt = con.createStatement();
            ResultSet   rs = stmt.executeQuery(count);

            if (rs.next()){
                books = new Book[rs.getInt(1)];
                rs = stmt.executeQuery(ids);
                int i = 0;
                while (rs.next()){
                    books[i++] = read(rs.getString(1));
                }//while
            }//if

        }//try
        
        catch (Exception e){
            throw new LibManException("LibManDAOImpl.readAll::" + 
                                       e.getMessage());
        }//catch

        return books;

    }//readAll()

    /** -----------------------------------------------------------------------
     * Update a book record, Not required for this application
     * But, it's part of the CRUD interface
     *
     * @param book  The book object to update
     * @throws libman.LibManException
     */
    public void update(Book book) throws LibManException {
        throw new UnsupportedOperationException(
                            "Not required for this application.");
    }

    /** -----------------------------------------------------------------------
     * Delete a book from the database
     * 
     * @param book      The book object to delete
     * @throws libman.LibManException
     */
    public void delete(Book book) throws LibManException {

        if (book != null && exists(Field.ID,book.getId())){
            String q = String.format("delete from app.books where " +
                                     "%s='%s'",
                                     getFieldName(Field.ID), 
                                     book.getId());
            try {
                Connection con = getConnInstance();
                Statement stmt = con.createStatement();
                System.err.printf("Rows Affected: %d\n", stmt.executeUpdate(q));
            }//try

            catch (Exception e){
                throw new LibManException("LibManDAOImpl.delete::" +
                                           e.getMessage());
            }//catch

        }//if

        else {
            throw new LibManException("LibManDAOImpl.delete::" +
                                      "Book ID " +
                                      ((book != null)?book.getId():"null") +
                                      " does not exist; Book Not Removed!");
        }//else

    }//delete()

    /**
     * Does a record exist for a specific field and value?
     *
     * @param field     The enumerated data field
     * @param value     The value to search
     * @return          true -- it exists; false -- it does not exist
     */
    public boolean exists(Field field, String value) {
        String q = String.format("select %s from app.books where %s='%s'",
                                    getFieldName(field),
                                    getFieldName(field),
                                    value);
        try {
            Connection con = getConnInstance();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(q);
            return rs.next();
        }//try

        catch (SQLException e) {
            System.err.println(String.format(
                    "LibManDAOImpl.exists()::Executing:\n%s\n", q));
            e.printStackTrace();
            return false;
        }//catch

    }//exists()

//Private Support Methods ****************************************************

    /** -----------------------------------------------------------------------
     * Create the Connection URL for the requested hostName and dataBase
     *
     * @return  A connection String used by the DriverManager
     */
    private String createConnUrl(){
        return String.format("jdbc:derby://%s:%s/%s", host, port, databaseName);
    }//createConnUrl()

    /** -----------------------------------------------------------------------
     * Abstract the Database Connector from the methods requiring it's use
     *
     * @return An instance of the database connector
     */
    private Connection getConnInstance(){
        return dbConn;
    }

    /** -----------------------------------------------------------------------
     * Get the name of the data field for it's enumerated counterpart
     *
     * @param field     The enumerated value
     * @return          The enumerated value's lowercase name
     */
    private String getFieldName(Field field){
        return field.name().toLowerCase();
    }//name4field()

}//LibManDAOImpl Class
